----------------------------------------------------
------ PAYROLL LOCK
----------------------------------------------------

CREATE OR REPLACE FUNCTION PAYROLL_LOCK RETURN NUMBER IS
lv_payroll_toggle CHAR(1);
BEGIN
	SELECT PAYROLL
	INTO lv_payroll_toggle
	FROM PAYROLL_PROCESSING;
	
	IF(lv_payroll_toggle = 'Y') THEN
		UPDATE PAYROLL_PROCESSING
		SET PAYROLL = 'N'
		WHERE PAYROLL = 'Y';
		RETURN 1;
	ELSE
		RETURN 0;
	END IF;
EXCEPTION
	WHEN OTHERS THEN
	RETURN 0;
END;
/

----------------------------------------------------
------ 
----------------------------------------------------

CREATE OR REPLACE FUNCTION PAYROLL_UNLOCK RETURN NUMBER IS
lv_payroll_toggle CHAR(1);
BEGIN
	SELECT PAYROLL
	INTO lv_payroll_toggle
	FROM PAYROLL_PROCESSING;
	
	IF(lv_payroll_toggle = 'N') THEN
		UPDATE PAYROLL_PROCESSING
		SET PAYROLL = 'Y';
		RETURN 1;
	ELSE
		RETURN 0;
	END IF;
EXCEPTION
	WHEN OTHERS THEN
		RETURN 0;
END;
/

----------------------------------------------------
------ month end lock
----------------------------------------------------

CREATE OR REPLACE FUNCTION Month_end_LOCK RETURN NUMBER IS
lv_month_end_toggle CHAR(1);
BEGIN
	SELECT Month_end
	INTO lv_month_end_toggle
	FROM PAYROLL_PROCESSING;
	
	IF(lv_month_end_toggle = 'Y') THEN
		UPDATE PAYROLL_PROCESSING
		SET Month_end = 'N'
		WHERE month_end = 'Y';
		RETURN 1;
	ELSE
		RETURN 0;
	END IF;
EXCEPTION
	WHEN OTHERS THEN
	RETURN 0;
END;
/


----------------------------------------------------
------ month end unlock
----------------------------------------------------

CREATE OR REPLACE FUNCTION Month_end_unLOCK RETURN NUMBER IS
lv_month_end_toggle CHAR(1);
BEGIN
	SELECT Month_end
	INTO lv_month_end_toggle
	FROM PAYROLL_PROCESSING;
	
	IF(lv_month_end_toggle = 'N') THEN
		UPDATE PAYROLL_PROCESSING
		SET Month_end = 'Y'
		WHERE month_end = 'N';
		RETURN 1;
	ELSE
		RETURN 0;
	END IF;
EXCEPTION
	WHEN OTHERS THEN
	RETURN 2;
END;
/

----------------------------------------------------
------ month end proc
----------------------------------------------------

CREATE OR REPLACE PROCEDURE month_end_proc IS
	CURSOR c_account IS
		SELECT *
		FROM account;
BEGIN
	FOR r_account in c_account LOOP
		IF (r_account.account_type_code = 'RE') THEN
			INSERT INTO new_transactions
			VALUES (wkis_seq.NEXTVAL,sysdate,'Month end zeroing for revenue',r_account.account_no,'D',r_account.account_balance);
			
			INSERT INTO new_transactions
			VALUES (wkis_seq.CURRVAL,sysdate,'Month end zeroing for revenue',5555,'C',r_account.account_balance);	
		END IF;
		
		IF (r_account.account_type_code = 'EX') THEN
			INSERT INTO new_transactions
			VALUES (wkis_seq.NEXTVAL,sysdate,'Month end zeroing for expense',r_account.account_no,'C',r_account.account_balance);
			
			INSERT INTO new_transactions
			VALUES (wkis_seq.CURRVAL,sysdate,'Month end zeroing for expense',5555,'D',r_account.account_balance);
			
		END IF;
	END LOOP;
END;
/

----------------------------------------------------
------ export csv file
----------------------------------------------------

CREATE OR REPLACE PROCEDURE export_data_proc(P_LOCATION VARCHAR2, P_FILENAME VARCHAR2 ) IS
	lv_file_handle UTL_FILE.file_type;
	CURSOR c_new_transactions IS
		SELECT *
		FROM new_transactions;
BEGIN
	lv_file_handle := UTL_FILE.FOPEN (P_LOCATION, P_FILENAME, 'w', 1024);
	
	FOR r_new_transactions in c_new_transactions LOOP
		UTL_FILE.PUT(lv_file_handle,r_new_transactions.transaction_no || ';' || 
						r_new_transactions.transaction_date || ';' || 
						r_new_transactions.description || ';' || 
						r_new_transactions.account_no || ';' || 
						r_new_transactions.transaction_type || ';' || 
						r_new_transactions.transaction_amount);
		UTL_FILE.NEW_LINE(lv_file_handle,1);
	END LOOP;
	
	UTL_FILE.FCLOSE(lv_file_handle);
END;
/

----------------------------------------------------
------ payroll load trigger
----------------------------------------------------

CREATE OR REPLACE TRIGGER payroll_load_trigger
Before INSERT ON payroll_load
FOR EACH ROW
BEGIN
	DBMS_OUTPUT.PUT_LINE('BEFORE');
	INSERT INTO NEW_TRANSACTIONS
	VALUES (WKIS_SEQ.NEXTVAL,:NEW.payroll_date,'Payroll credits to '||:NEW.employee_id,2050,'C',:NEW.amount);
	
	INSERT INTO NEW_TRANSACTIONS
	VALUES (WKIS_SEQ.CURRVAL,:NEW.payroll_date,'Payroll debits to '||:NEW.employee_id,4045,'D',:NEW.amount);
	
	:NEW.status := 'G';
	
EXCEPTION
	WHEN OTHERS THEN
		:NEW.status := 'B';
END;
/

----------------------------------------------------
------ access trigger
----------------------------------------------------

CREATE OR REPLACE TRIGGER access_trigger
AFTER DELETE OR INSERT OR UPDATE ON ACCOUNT
FOR EACH ROW

BEGIN
	IF UPDATING THEN
		INSERT INTO access_log
		VALUES(USER, SYSDATE, 'UPDATE');
	ELSIF INSERTING THEN
		INSERT INTO access_log
		VALUES(USER,  SYSDATE, 'INSERT');               
    ELSIF DELETING THEN
	    INSERT INTO access_log
	    VALUES(USER, SYSDATE, 'DELETE');
    END IF;
END;
/

commit;